package com.lei.springbootpractice.dao;

import com.lei.springbootpractice.pojo.NotificationStatusPojo;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface NotificationStatusMapper {

    /**
     * 获取用户未读通知数量
     */
    @Select("SELECT COUNT(*) FROM notification_status WHERE user_id = #{userId} AND is_read = FALSE")
    int getUnreadCount(@Param("userId") String userId);

    /**
     * 获取用户未读通知列表
     */
    @Select("SELECT * FROM notification_status " +
            "WHERE user_id = #{userId} AND is_read = FALSE " +
            "ORDER BY created_time DESC LIMIT #{limit}")
    List<NotificationStatusPojo> getUnreadNotifications(@Param("userId") String userId, 
                                                       @Param("limit") int limit);

    /**
     * 获取用户最新的通知（包括已读和未读）
     */
    @Select("SELECT * FROM notification_status " +
            "WHERE user_id = #{userId} " +
            "ORDER BY created_time DESC LIMIT #{limit}")
    List<NotificationStatusPojo> getLatestNotifications(@Param("userId") String userId, 
                                                       @Param("limit") int limit);

    /**
     * 标记指定通知为已读
     */
    @Update("UPDATE notification_status SET is_read = TRUE, updated_time = CURRENT_TIMESTAMP " +
            "WHERE id = #{id}")
    int markAsRead(@Param("id") Integer id);

    /**
     * 标记用户所有通知为已读
     */
    @Update("UPDATE notification_status SET is_read = TRUE, updated_time = CURRENT_TIMESTAMP " +
            "WHERE user_id = #{userId} AND is_read = FALSE")
    int markAllAsRead(@Param("userId") String userId);

    /**
     * 标记用户特定类型的通知为已读
     */
    @Update("UPDATE notification_status SET is_read = TRUE, updated_time = CURRENT_TIMESTAMP " +
            "WHERE user_id = #{userId} AND notification_type = #{notificationType} AND is_read = FALSE")
    int markTypeAsRead(@Param("userId") String userId, 
                      @Param("notificationType") String notificationType);

    /**
     * 标记用户特定对话的通知为已读
     */
    @Update("UPDATE notification_status SET is_read = TRUE, updated_time = CURRENT_TIMESTAMP " +
            "WHERE user_id = #{userId} AND related_id = #{relatedId} AND is_read = FALSE")
    int markConversationAsRead(@Param("userId") String userId, 
                              @Param("relatedId") String relatedId);

    /**
     * 手动插入通知（如果触发器不工作时的备用方案）
     */
    @Insert("INSERT INTO notification_status (user_id, user_role, notification_type, " +
            "related_id, message_content, sender_name, is_read, created_time, updated_time) " +
            "VALUES (#{userId}, #{userRole}, #{notificationType}, #{relatedId}, " +
            "#{messageContent}, #{senderName}, #{isRead}, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)")
    int insertNotification(NotificationStatusPojo notification);

    /**
     * 删除旧通知（清理数据）
     */
    @Delete("DELETE FROM notification_status WHERE created_time < DATEADD('DAY', -#{days}, CURRENT_TIMESTAMP)")
    int deleteOldNotifications(@Param("days") int days);

    /**
     * 获取用户特定类型的未读通知数量
     */
    @Select("SELECT COUNT(*) FROM notification_status " +
            "WHERE user_id = #{userId} AND notification_type = #{notificationType} AND is_read = FALSE")
    int getUnreadCountByType(@Param("userId") String userId, 
                           @Param("notificationType") String notificationType);

    /**
     * 检查是否有新通知（用于轮询优化）
     */
    @Select("SELECT COUNT(*) FROM notification_status " +
            "WHERE user_id = #{userId} AND is_read = FALSE AND created_time > #{lastCheckTime}")
    int hasNewNotificationsSince(@Param("userId") String userId, 
                               @Param("lastCheckTime") String lastCheckTime);

    /**
     * 获取最新通知的时间戳（用于轮询优化）
     */
    @Select("SELECT MAX(created_time) FROM notification_status WHERE user_id = #{userId}")
    String getLatestNotificationTime(@Param("userId") String userId);
} 